/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package daoBeans;

import java.sql.*;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import modelo.Usuario;

/**
 *
 * @author Camilo
 */
public class daoUsuario {
    
    Connection link;
    Statement st;
    PreparedStatement pst;
    ResultSet rs;
    
    //Conexión con MySQL

    public daoUsuario(Connection link) {
        this.link = link;
        try {
            st = link.createStatement();
           
        } catch (SQLException ex) {
            Logger.getLogger(daoUsuario.class.getName()).log(Level.SEVERE, null, ex);
        }
    }
    
    //Creación
    
    public int crearUsuario(Usuario u){
        int n = 1;
        try {
            pst = link.prepareStatement("INSERT INTO Usuario(Nombre, ID, Rol, Empresa, Username, Password) " +
               "VALUES(?, ?, ?, ?, ?, ?)");
            pst.setString(1, u.getNombre());
            pst.setString(2, u.getId());
            pst.setString(3, u.getRol());
            pst.setString(4, u.getEmpresa());
            pst.setString(5, u.getUsername());
            pst.setString(6, u.getPassword());
            n = pst.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(daoUsuario.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return n;
    }
    
    //Búsqueda
    
    public ArrayList<Usuario> buscarUsuarioNombre(String Nombre){
        ArrayList<Usuario> us = new ArrayList<Usuario>();
                
        try {
            rs = st.executeQuery("SELECT Nombre, ID, Rol, Empresa, Username, Password FROM usuario " +
                    "WHERE Nombre LIKE '%"+Nombre+"%'");
        } catch (SQLException ex) {
            Logger.getLogger(daoUsuario.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
            while(rs.next()){
                Usuario u = new Usuario();
                u.setNombre(rs.getObject(1).toString());
                u.setId(rs.getObject(2).toString());
                u.setRol(rs.getObject(3).toString());
                u.setEmpresa(rs.getObject(4).toString());
                u.setUsername(rs.getObject(5).toString());
                u.setPassword(rs.getObject(6).toString());
                us.add(u);
            }
        } catch (SQLException ex) {
            Logger.getLogger(daoUsuario.class.getName()).log(Level.SEVERE, null, ex);
        } 
        
        return us;
    }
    
    public Usuario buscarUsuarioUsername(String Username){

         Usuario u = new Usuario();
        
        try {
            rs = st.executeQuery("select * from Usuario where Username = '"+Username+"'");
        } catch (SQLException ex) {
            Logger.getLogger(daoUsuario.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
            rs.first();
            u.setNombre(rs.getObject(1).toString());
            u.setId(rs.getObject(2).toString());
            u.setRol(rs.getObject(3).toString());
            u.setEmpresa(rs.getObject(4).toString());
            u.setUsername(rs.getObject(5).toString());
            u.setPassword(rs.getObject(6).toString());
        } catch (SQLException ex) {
            Logger.getLogger(daoUsuario.class.getName()).log(Level.SEVERE, null, ex);
        }        
        
        return u;
        
    }   
    
    public Usuario buscarUsuarioID(String ID){

        Usuario u = new Usuario();
        
        try {
            rs = st.executeQuery("select * from usuario where ID = '"+ID+"'");
        } catch (SQLException ex) {
            Logger.getLogger(daoUsuario.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
            rs.first();
            u.setNombre(rs.getObject(1).toString());
            u.setId(rs.getObject(2).toString());
            u.setRol(rs.getObject(3).toString());
            u.setEmpresa(rs.getObject(4).toString());
            u.setUsername(rs.getObject(5).toString());
            u.setPassword(rs.getObject(6).toString());
        } catch (SQLException ex) {
            Logger.getLogger(daoUsuario.class.getName()).log(Level.SEVERE, null, ex);
        }        
        
        return u;
        
    } 
    
    public ArrayList<Usuario> buscarUsuarioEmpresa(String Empresa){
        ArrayList<Usuario> us = new ArrayList<Usuario>();
                
        try {
            rs = st.executeQuery("SELECT Usuario.Nombre, Usuario.ID, Usuario.Rol " +
                    "Usuario.Empresa, Usuario.Username, Usuario.Password FROM usuario " +
                    "inner join Usuario on Empresa.NIT = Usuario.ID " +
                    "WHERE Usuario.Empresa LIKE '%"+Empresa+"%'");
        } catch (SQLException ex) {
            Logger.getLogger(daoUsuario.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
            while(rs.next()){
                Usuario u = new Usuario();
                u.setNombre(rs.getObject(1).toString());
                u.setId(rs.getObject(2).toString());
                u.setRol(rs.getObject(3).toString());
                u.setEmpresa(rs.getObject(4).toString());
                u.setUsername(rs.getObject(5).toString());
                u.setPassword(rs.getObject(6).toString());
                us.add(u);
            }
        } catch (SQLException ex) {
            Logger.getLogger(daoUsuario.class.getName()).log(Level.SEVERE, null, ex);
        } 
        
        return us;
    }
    
    public ArrayList<Usuario> buscarUsuarioRol(String Rol, String Empresa){
        ArrayList<Usuario> us = new ArrayList<Usuario>();
                
        try {
            rs = st.executeQuery("SELECT Usuario.Nombre, Usuario.ID, Usuario.Rol " +
                    "Usuario.Empresa, Usuario.Username, Usuario.Password FROM usuario " +
                    "inner join Usuario on Empresa.NIT = Usuario.ID " +
                    "WHERE Usuario.ID LIKE '%"+Empresa+"%' AND Usuario.Rol LIKE '%"+Rol+"%'");
        } catch (SQLException ex) {
            Logger.getLogger(daoUsuario.class.getName()).log(Level.SEVERE, null, ex);
        }
        try {
            while(rs.next()){
                Usuario u = new Usuario();
                u.setNombre(rs.getObject(1).toString());
                u.setId(rs.getObject(2).toString());
                u.setRol(rs.getObject(3).toString());
                u.setEmpresa(rs.getObject(4).toString());
                u.setUsername(rs.getObject(5).toString());
                u.setPassword(rs.getObject(6).toString());
                us.add(u);
            }
        } catch (SQLException ex) {
            Logger.getLogger(daoUsuario.class.getName()).log(Level.SEVERE, null, ex);
        } 
        
        return us;
    }
    
    //Actualizar
    
    public int actualizarUsuario(Usuario u){
        int n = 0;
        try {
            pst = link.prepareStatement("UPDATE Usuario "
                    + "SET "
                    + "Nombre = ? "
                    + "ID = ? "
                    + "Rol = ? "
                    + "Empresa = ? "
                    + "Username = ? "
                    + "Password = ? "
                    + "Where ID = " + u.getId());
            pst.setString(1, u.getNombre());
            pst.setString(2, u.getId());
            pst.setString(3, u.getRol());
            pst.setString(4, u.getEmpresa());
            pst.setString(5, u.getUsername());
            pst.setString(5, u.getPassword());
            n = pst.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(daoUsuario.class.getName()).log(Level.SEVERE, null, ex);
        }        
        return n;
    }
    
    //Eliminar
    
    public int eliminarUsuario(String ID){
        
        int n = 0;
        
        try {
            st.executeUpdate("DELETE from Usuario WHERE NIT = '" + ID + "'");
            n = 1;
        } catch (SQLException ex) {
            Logger.getLogger(daoEmpresa.class.getName()).log(Level.SEVERE, null, ex);
        }
        
        return n;
        
    }    
}
